package com.summer.dao;

import com.summer.bean.Course;
import com.summer.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * Description:
 * User: 35391
 * Package: com.summer.dao
 * Date: 2025/7/22
 * Time: 下午9:06
 * Version: 1.0
 */
public class CourseDao {
    public static String getCourseDetailById(Integer id){
        Connection conn = null;
        PreparedStatement ps = null;
        String result = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select information from course_detail where id = ?;";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if(rs.next()){
                result = rs.getString("information");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return result;
    }

    public static List<Course> GetRelativeDetailById(Integer id){
        ArrayList<Course> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select cno, cname, credit from course where relative_id = ?;";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Course(rs.getString("cno"), rs.getString("cname"), rs.getString("credit")));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return list;
    }

    public static List<Course> selectAllCourse(){
        ArrayList<Course> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select cno, cname, credit from course;";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Course(rs.getString("cno"), rs.getString("cname"), rs.getString("credit")));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return list;
    }

    public static List<Course> selectByLikeName(String key){
        ArrayList<Course> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from course where cname like ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + key + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Course(rs.getString("cno"), rs.getString("cname"), rs.getString("credit")));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return list;
    }

    public static List<Course> selectByLikeCno(String key){
        ArrayList<Course> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from course where cno like ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + key + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Course(rs.getString("cno"), rs.getString("cname"), rs.getString("credit")));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return list;
    }

    public static List<Course> selectCourseByUsername(String username){
        ArrayList<Course> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT c.cno, c.cname, c.credit FROM user u LEFT JOIN enrol e ON u.sno = e.sno LEFT JOIN course c ON e.cno = c.cno WHERE u.username = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Course(rs.getString("cno"), rs.getString("cname"), rs.getString("credit")));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return list;
    }

    public static int enrolCourse(String username, String cno){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO enrol (sno, cno) SELECT sno, ? FROM user WHERE username = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, cno);
            ps.setString(2, username);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return result;
    }

    public static int dropCourse(String username, String cno){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from enrol where sno = (select sno from user where username = ?) and cno = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, cno);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return result;
    }

    public static int selectEnrol(String username, String cno){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        try {
            conn = DBUtil.getConnection();
            String sql = "select count(*) from enrol e join user u on e.sno = u.sno where u.username = ? and e.cno = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, cno);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return result;
    }
}
